<?php
$serv=$_POST['serv'];
$usr=$_POST['usr'];
$pwd=$_POST['pwd'];
$pfx=$_POST['pfx'];
$dsusr=$_POST['dsusr'];
$dspwd=$_POST['dspwd'];
$lang=$_POST['lang'];
$appusr=$_POST['appusr'];
$apppwd=$_POST['apppwd'];
$inst=$_POST['inst'];

if ($lang == "1"){
	include('../includes/language/german.php');
} elseif ($lang == "2"){
	include('../includes/language/english.php');
} elseif ($lang == "3"){
	include('../includes/language/french.php');
}

include ('setup_head.html');
?>
<div id="ipage3">
	<?php echo "<h1>$nls_install_headline</h1>"; ?>
	<?php echo "<h2>$nls_install_step4</h2>"; ?>
	<?php echo "<p>$nls_install_step4_explain</p>"; ?>
	
	<?php
		echo "Connecting to mySQL using Root-User... ";
		if (!($db = mysql_connect ($srv, $usr, $pwd))){
			echo mysql_error()."<br/>";
			break;
		} else {
		echo "OK<br/>";
		}

		echo "Creating the new Database-User... ";
		$sql_addusr = "CREATE USER '$dsusr' IDENTIFIED BY '$dspwd';";
		if (!(mysql_query($sql_addusr))){
			echo mysql_error()."<br/>";		
		} else {
		echo "OK<br/>";			
		}
		
		echo "Creating Database... ";
		$pfx=$pfx."science";
		$sql_createdb = "CREATE DATABASE $pfx;";
		if (!(mysql_query($sql_createdb))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Selecting Database... ";
		$sql_selectdb = "USE $pfx;";
		if (!(mysql_query($sql_selectdb))){
			echo mysql_error()."<br/>";
			break;
		} else {
			echo "OK<br/>";
		}
		
		echo "Creating Table staff... ";
		if (!(mysql_query('CREATE TABLE staff (
												id_staff	SMALLINT NOT NULL AUTO_INCREMENT,
												username	VARCHAR(30) NOT NULL,
												password	VARCHAR(30) NOT NULL,
												lastname	VARCHAR(30) NOT NULL,
												firstname	VARCHAR(30) NOT NULL,
												language	CHAR(1),
												userlevel	CHAR(1),
												ward		VARCHAR(30),
												PRIMARY KEY(id_staff)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		mysql_query ("insert into staff set id_staff = '9999', username='dicom', password = 'dicom', lastname='DICOM', firstname='Image', language='1', userlevel='7'");
		
		echo "Creating Table blog... ";
		if (!(mysql_query('CREATE TABLE blog (
												id_blog		INT NOT NULL AUTO_INCREMENT,
												id_staff	SMALLINT,
												blogdate	INT,
												message		TEXT,
												PRIMARY KEY(id_blog)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Creating Table organ... ";
		if (!(mysql_query('CREATE TABLE organ (
												id_organ	SMALLINT NOT NULL AUTO_INCREMENT,
												organ_name	TINYTEXT,
												PRIMARY KEY(id_organ)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Creating Table reports... ";
		if (!(mysql_query('CREATE TABLE reports (
												id_report	INT NOT NULL AUTO_INCREMENT,
												headline	TINYTEXT,
												blogline	TINYTEXT,
												diagnosis	TEXT,
												report		TEXT,
												last_modified	INT,
												id_staff	SMALLINT,
												images		TINYTEXT,
												id_case		INT,	
												status		SMALLINT,	
												triage		TINYTEXT,
												PRIMARY KEY(id_report)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Creating Index for Table reports... ";
		if (!(mysql_query('CREATE INDEX index_report ON reports (report(500));'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Creating Table cases... ";
		if (!(mysql_query('CREATE TABLE cases (
												id_case		INT NOT NULL AUTO_INCREMENT,
												editor		SMALLINT,
												last_modified	INT,
												topic		TINYTEXT,
												status		SMALLINT,
												triage				TINYTEXT,
												PRIMARY KEY(id_case)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Creating Table creps... ";
		if (!(mysql_query('CREATE TABLE creps (
												id_crep		SMALLINT NOT NULL AUTO_INCREMENT,
												id_case		INT,
												id_report	INT,
												PRIMARY KEY(id_crep)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Creating Table c_media... ";
		if (!(mysql_query('CREATE TABLE c_media (
												id_c_media		INT NOT NULL AUTO_INCREMENT,
												id_case		INT,
												id_media		INT,
												media_type	VARCHAR(10),
												PRIMARY KEY(id_c_media)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Creating Table comments... ";
		if (!(mysql_query('CREATE TABLE comments (
												id_comment	INT NOT NULL AUTO_INCREMENT,
												id_staff		SMALLINT,
												id_case		INT,
												comment		TEXT,
												posted		INT,
												PRIMARY KEY(id_comment)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Creating Table study... ";
		if (!(mysql_query('CREATE TABLE study (
												id_study	INT NOT NULL AUTO_INCREMENT,
												patient_id			TINYTEXT,
												modality			VARCHAR(10),
												study_description	TINYTEXT,
												study_instance_uid	TINYTEXT,
												organ				VARCHAR(10),
												ref_phys			TINYTEXT,
												tags				TINYTEXT,
												upload_date			TINYTEXT,
												triage				TINYTEXT,
												acc_no				TINYTEXT,
												manufacturer		TEXT,
												PRIMARY KEY(id_study)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Creating Table series... ";
		if (!(mysql_query('CREATE TABLE series (
												id_series	INT NOT NULL AUTO_INCREMENT,
												study_instance_uid	TINYTEXT,
												series_instance_uid	TINYTEXT,
												series_description	TINYTEXT,	
												modality			VARCHAR(10),
												organ				TINYTEXT,
												upload_user			SMALLINT,
												series_number		SMALLINT,	
												tags				TINYTEXT,					
												reports				TINYTEXT,
												triage				TINYTEXT,
												upload_date			INT,
												created_video		VARCHAR(2),
												PRIMARY KEY(id_series)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Creating Table image... ";
		if (!(mysql_query('CREATE TABLE image (
												id_image			INT NOT NULL AUTO_INCREMENT,
												study_instance_uid	TINYTEXT,
												series_instance_uid	TINYTEXT,
												sop_instance_uid	TEXT,
												tags				TINYTEXT,
												modality			VARCHAR(10),
												organ				TINYTEXT,
												location_thumb		TINYTEXT,
												location_image		TINYTEXT,
												imgrep				TINYTEXT,
												thbrep				TINYTEXT,
												series_number		SMALLINT,
												instance_number		SMALLINT,
												id_staff			SMALLINT,
												reports				TINYTEXT,
												date				INT,
												window_center		INT,
												window_width		INT,
												laterality			TINYTEXT,
												image_comment		TEXT,
												contrast			TINYTEXT,
												mr_aquisitiontype	TEXT,
												sequence_name		TEXT,
												slice_thickness		INT,
												kv					INT,
												protocol_name		TEXT,
												kernel				TINYTEXT,
												mr_coil				TINYTEXT,
												flip_angle			INT,
												pat_pos				TINYTEXT,
												sens				INT, 
												triage				TINYTEXT,
												pat_id				TEXT,
												manufacturer		TEXT,
												upload_user			SMALLINT,
												upload_date			INT,
												created_video		VARCHAR(2),
												PRIMARY KEY(id_image)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Creating Table patient... ";
		if (!(mysql_query('CREATE TABLE patient (
												id_patient			INT NOT NULL AUTO_INCREMENT,
												pat_id				TEXT,
												pat_sex				TINYTEXT,
												pat_age				TINYTEXT,
												pat_weight			TINYTEXT,
												PRIMARY KEY(id_patient)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Creating Table galleries... ";
		if (!(mysql_query('CREATE TABLE galleries (
												id_gal				INT NOT NULL AUTO_INCREMENT,
												name				TINYTEXT,
												editor				SMALLINT,
												date				INT,
												PRIMARY KEY(id_gal)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			
			echo "OK<br/>";
		
		}
		
		echo "Creating Table content... ";
		if (!(mysql_query('CREATE TABLE content (
												id_cont				INT NOT NULL AUTO_INCREMENT,
												gal					INT,
												image				SMALLINT,
												PRIMARY KEY(id_cont)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Creating Table media... ";
		if (!(mysql_query('CREATE TABLE media (
												id_media			INT NOT NULL AUTO_INCREMENT,
												study_instance_uid	TINYTEXT,
												series_instance_uid	TINYTEXT,
												tags				TINYTEXT,
												modality			VARCHAR(10),
												organ				TINYTEXT,
												location_thumb		TINYTEXT,
												location_media		TINYTEXT,
												imgrep				TINYTEXT,
												medrep				TINYTEXT,
												id_staff			SMALLINT,
												upload_date			INT,
												upload_user			SMALLINT,
												series_number		SMALLINT,
												pat_id				TEXT,
												media_type			VARCHAR(10),
												PRIMARY KEY(id_media)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Granting all priveleges on Database $pfx to user $dsusr... ";
		$sql_grant = "GRANT ALL ON ".$pfx.".* TO ".$dsusr."@".$serv." IDENTIFIED BY '".$dspwd."';";
		if (!(mysql_query($sql_grant))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		echo "Adding the admin-user to DicomScience... ";
		$sql_addadmin = "INSERT INTO staff(username, password, lastname, firstname, userlevel) VALUES ('$appusr', '$apppwd', 'Administrator', 'DicomScience', '9');";
		if(!(mysql_query($sql_addadmin))){
			echo mysql_error()."<br/>";
			break;
		} else {
			echo "OK<br/>";
		}
		
// only for testing purposes:
mysql_query ("INSERT INTO staff (username, password, lastname, firstname, userlevel, language) VALUES ('rad', 'rad', 'Demouser', 'Level7', '7', '1')");

		echo "Adding default DICOM organs... ";
		if (!(mysql_query ("INSERT INTO organ(organ_name) VALUES ('SKULL')"))){
			echo mysql_error();
			break;
		} else {
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('HEAD')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('JAW')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('CSPINE')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('NECK')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('TSPINE')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('LSPINE')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('SSPINE')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('COCCYX')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('CHEST')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('HEART')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('CLAVICLE')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('BREAST')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('ABDOMEN')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('PELVIS')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('HIP')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('SHOULDER')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('ELBOW')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('KNEE')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('ANKLE')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('ARM')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('HAND')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('FOOT')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('EXTREMITY')");
		mysql_query ("INSERT INTO organ(organ_name) VALUES ('LEG')");		
		echo "OK<br/>";
		}
		
		echo "Creating Table settings... ";
		if (!(mysql_query('CREATE TABLE settings (
												id_setting		SMALLINT NOT NULL AUTO_INCREMENT,
												language		SMALLINT,
												PRIMARY KEY(id_setting)
												);'))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
		
		$sql_lang = "INSERT INTO settings (language) VALUES ('$lang');";
		echo "Setting System Language... ";
		if (!(mysql_query($sql_lang))){
			echo mysql_error()."<br/>";
		} else {
			echo "OK<br/>";
		}
			
		/*
		 * For Finalisation, a "global_settings.php"- File will be written into /var/www/includes.
		 * Contents: Image Location, Thumbnail location, Database Connection and designated Database - User.
		 */
		
		echo "Writing configuration data... ";
		$cset = "<"."?php 
	$"."img_store = '/medImages'; 
	"."$"."thb_store = '/medThumbs'; 
	"."$"."img_rep = '/var/www/medImages'; 
	"."$"."thb_rep = '/var/www/medThumbs'; 
	"."$"."institute = '$inst'; 
	"."$"."lang = '$lang'; 
	"."$"."serv = '$serv'; 
	"."$"."dbase = '$pfx';
	"."$"."dbusr = '$dsusr'; 
	"."$"."dbpwd = '$dspwd';
?".">";
		echo $cset;
		$conf=fopen('../includes/gset.php','w');
			fwrite ($conf, $cset);
		fclose($conf);	
	?>
	<h3>done!</h3>
<?php include ('../includes/database/closedb.php'); ?>	
<?php include ('setup_foot.html'); ?>